"""
.. module:: sblab
    :platform: Unix, Windows
    :synopsis: Miscellanea of functions to interact with sblab db

.. moduleauthor:: Dario Beraldi <dario.beraldi@cancer.org.uk>

Check out the module source code from the svn repository `here <http://code.google.com/p/postgresql-setup-cruk/source/browse/trunk/pysblab>`_.

.. note::
    If you change the sblab module update this documentation by executing::
    
        cd ~/svn_checkout/postgresql-setup-cruk/pysblab/doc
        make html

.. seealso::
    Trunk `postgresql-setup-cruk <http://code.google.com/p/postgresql-setup-cruk/source/browse/trunk>`_ for several programs
    used by this module
    
    Minimal `quickstart <http://scienceoss.com/minimal-sphinx-setup-for-autodocumenting-python-modules/>`_ with sphinx and official
    documentation to `sphinx <http://packages.python.org/an_example_pypi_project/sphinx.html>`_

Module documentation
====================
"""

import re
import os
import sys
import subprocess
import gzip
import psycopg2
import inspect
import pprint

""" Miscellanea of functions to interact with sblab db """

def get_psycopgpass():
    """Read file ``~/.psycopgpass`` to get the connection string to pass to `psycopg2.connect() <http://packages.python.org/psycopg2/module.html#psycopg2.connect>`_.
    
    **Returns:**  A *string* of connection parameters to `psycopg2.connect() <http://packages.python.org/psycopg2/module.html#psycopg2.connect>`_.

    ``get_psycopgpass()`` will get the first non-blank, non-commented (#) line from ``~/.psycopgpass``. Therefore, ``~/.psycopgpass`` might look like::
    
            # Config file to access postgres from psycopg
            # The application (e.g. project_files.py) will read ~/.psycopgpass and pass the first
            # uncommented, non blank line to psycopg2.connect() as *string*.        
    
            dbname=sblab user=myusername host=12.34.56.78 password=mypassword
    
    and ``get_psycopgpass()`` will return: ``"dbname=sblab user=myusername host=12.34.56.78 password=mypassword"``

    .. seealso::
        :func:`.get_dbpass`

    
    """
    conn= open(os.path.join(os.getenv("HOME"), '.psycopgpass'))
    conn= conn.readlines()
    conn_args= [x.strip() for x in conn if x.strip() != '' and x.strip().startswith('#') is False][0]
    return(conn_args)

def get_dbpass():
    """Read file ``~/.psycopgpass`` to get the connection arguments to connect to sblab
    
    **Returns** A *dictionary* of connection parameters.
    
    The returned dict looks like::
    
        {'host': 'xx.zz.yy.qq', 'dbname': 'mydb', 'user': 'myself'}
        
    .. seealso::
        :func:`.get_psycopgpass`
    """    
    import os
    conn= open(os.path.join(os.getenv("HOME"), '.psycopgpass'))
    conn= conn.readlines()
    conn_args= [x.strip() for x in conn if x.strip() != '' and x.strip().startswith('#') is False][0]
    conn_args= conn_args.split(' ')
    conn_dict= {}
    for x in conn_args:
        xc= x.split('=')
        conn_dict[xc[0]]= xc[1] 
    return(conn_dict)

def parse_sample_name(x):
    """Parse the string ``x`` to return the following componenents as a list or exception warning if sample name is invalid: 
    
    1. User initials
    2. Incremental id following user initials
    3. Description string of sample name.
    
    Returns these three components as list .
    
    **Example**::
    
        >>> parse_sample_name('db001_mysample')
        ['db', '001', mysample]
        
        ## Check sample name is syntatically correct:
        >>> parse_sample_name('123')
        Traceback (most recent call last):
          File "<stdin>", line 1, in <module>
          File "/Users/berald01/svn_checkout/postgresql-setup-cruk/pysblab/sblab.py", line 101, in parse_sample_name
            raise Warning('Invalid sample name: "%s" does not start with two or more letters.' %(x))
        Warning: Invalid sample name: "123" does not start with two or more letters.
    
    """
    if type(x) != str:
        sys.exit(inspect.stack()[0][3] + ': x is not of type string: %s' %(x))
    ii= re.match('^[a-zA-Z]{2,}', x)
    if ii is None:
        raise Warning('Invalid sample name: "%s" does not start with two or more letters.' %(x))
        return(None)
    increment_start= ii.end()
    id= re.match('\d{3,}', x[increment_start:])
    if id is None:
        raise Warning('Warning: Invalid sample name: "%s" three or more digits must follow the initials ("%s")' %(x, ii.group()))
        return(None)
    descr_start= id.end()
    descr= x[increment_start + descr_start:]
    return([ii.group(), id.group(), descr])

def convert_id(x, direction= 'to_lib'):
    """Convert id x from 'library' format to 'sample' format or vice versa
    by changing the capitalization of the initials.
    
    Kwargs:
        direction (str): One of ``['to_sample', 'to_lib']`` to conevrt `x` to sample or
        library convention
    
    **Example**::
    
        >>> convert_id('DB001_mysample', direction= 'to_lib')
        'db001_mysample'
        >>> convert_id('db001_mysample', direction= 'to_sample')
        'DB001_mysample'
        
    """
    valdir= ['to_sample', 'to_lib']
    x= parse_sample_name(x) ## This will be ['DB', '001', '_mysample']
    if direction == 'to_lib':
        x[0]= x[0].lower()
    elif direction == 'to_sample':
        x[0]= x[0].upper()
    else:
        sys.exit(inspect.stack()[0][3] +  ': Invalid direction: "%s". Allowed is %s' %(direction, valdir))
    z= ''.join(x)
    return(z)


def validate_initials(lst):
    """Check initials in list lst are registred in table sblab.main.contact.initials
    - Valid match is case insensitve
    Return a list of True or False for each element in lst present or absent
    lst: List or Tuple of initials to validate. If string it will be converted to list [lst]
    """
    if type(lst) == str:
        lst= [lst]
    lst= [x.lower() for x in lst]
    conn= psycopg2.connect(get_psycopgpass())
    cur= conn.cursor()
    sql= 'SELECT initials FROM contacts'
    cur.execute(sql)
    cur.close()
    conn.close()
    ii= [(x[0]).lower() for x in cur.fetchall()]
    vlist= []
    for x in lst:
        if x in ii:
            vlist.append(True)
        else:
            vlist.append(False)
    return(vlist)

def get_lastid(ii, table, column, case_sensitive= False):
    """For initial **ii** query **table.column** to get the last id.
    
    **Args:**
        ii (str): Initials for which to get the last id
        
        table (str): Table to query. Typically samples or libraries
        
        column (str): Column with ids. Typcally sample_id, library_id
    **Kwargs:**
        case_sensitive (bool): Should the match between ii and table.column be case sensitive (default False)
    
    **Returns:**
        int for the latest initials in ii in table.column or None for no match.

    E.g. if in table ``samples``, column ``sample_id`` you have these IDs: ``['mb001', 'db001', 'db002', 'db010']``::
    
        >>> get_lastid('db', 'samples', 'sample_id')
        10
    
    **IMPORTANT**: The incrememt is interpreted as integer not as alphanumeric.
    
    So: '001' > '000100' = int('001') > int('000100') = 1 > 100 = False
        
    .. warning::
        Not sure if this function is reliable.
    """
    if case_sensitive is False:
        ii= ii.lower()
    conn= psycopg2.connect(get_psycopgpass())
    cur= conn.cursor()
    sql= 'SELECT "%s" FROM "%s"' %(column, table)
    cur.execute(sql)
    sample_names= [x[0] for x in cur.fetchall()]
    if case_sensitive is False:
        sample_names= [x.lower() for x in sample_names]
    cur.close()
    conn.close()
    samples_ii= [x for x in sample_names if x.startswith(ii)]
    if len(samples_ii) == 0:
        return(None)
    samples_incr= sorted([int(parse_sample_name(x)[1]) for x in samples_ii])
    lastid= samples_incr[-1]
    return(lastid)

def uplod_demux_fuzzy_report(reportname):
    """Uplaod to sblab table demux_report the report produced by demux_fuzzy.py.
    
    Args:
        reportname (str): Name of report file to uplaod
    Returns:
        Nothing is returned
        
    **TODO:** Use psycopg module instead of ssh/scp
    
    """
    repbasename= os.path.split(reportname)[1]
    dbpass= get_dbpass()
    cmd= 'scp %s %s:/tmp/' %(reportname, dbpass['host'])
    print(cmd)
    p= subprocess.Popen(cmd, shell= True)
    p.wait()
    
    cmd= '''ssh %s "source ~/.bash_profile;
    psql -U %s %s -c \\"
    CREATE TEMP TABLE demux_report_tmp (LIKE demux_report INCLUDING DEFAULTS);
    COPY demux_report_tmp FROM '/tmp/%s' WITH CSV DELIMITER E'\t';
    INSERT INTO demux_report SELECT * FROM demux_report_tmp EXCEPT SELECT * FROM demux_report;
    DROP TABLE demux_report_tmp; 
    \\"" ''' %(dbpass['host'], dbpass['user'], dbpass['dbname'], repbasename)
    print(cmd)
    p= subprocess.Popen(cmd, shell= True)
    p.wait()

# ----------------------------------------------------------------
# Mostly functions to interact with CRI lims
# ----------------------------------------------------------------

def slx2filepath(slx_id):
    """Return a list with the URL to the files associated to the given slx_id (SLX-ID)
    in alphanumeric order and duplicates removed.
    NB: Demultiplexed files are returned as well!

    The returned list typically looks like::
        
        >>> slx2filepath('SLX-9999')
        ['uk-cri-lsol01.crnet.org:/solexa02/data/Runs/120419_HWI-ST230_822_D0U6DACXX/full_Data/Intensities/Bustard1.9.0_02-05-2012_solexa/GERALD_02-05-2012_solexa/s_1_1_sequence.txt.gz']
    """
    cmd= 'getFilesForLibrary.py %s' %(slx_id)
    p= subprocess.Popen(cmd, shell= True, stdout=subprocess.PIPE)
    
    p.wait()
    fastq_path= p.stdout.readlines()
    fastq_path= sorted(set([x.strip() for x in fastq_path]))
    fastq_path= [x for x in fastq_path if x != '']
    return(fastq_path)

def get_runid(solexapath):
    """Extract the **run id** from the URL passed as string.
    
    See the function code for the regular expression used to extract the run ID.

    **Example**::
        
        >>> get_runid('uk-cri-lsol01.crnet.org:/solexa02/data/Runs/120419_HWI-ST230_822_D0U6DACXX/full_Data/Intensities/Bustard1.9.0_02-05-2012_solexa/GERALD_02-05-2012_solexa/s_1_1_sequence.txt.gz')
        '120419_HWI-ST230_822_D0U6DACXX'

    """
    if solexapath == '':
        sys.exit(inspect.stack()[0][3] + ': Empty path to fastqfile!')
    ori_path= solexapath
    folders=[]
    while 1:
        solexapath,folder=os.path.split(solexapath)
        if folder!="":
            folders.append(folder)
        else:
            if solexapath!="":
                folders.append(solexapath)
            break
    folders.reverse()    
    runre= re.compile('^\d{6}_[A-Za-z0-9\-]+_\d{1,}_[\w\-]+$') ## Regex to match runID. Composed by: <date>_<instrument id>_<lane id>_<flowcell id>
    for d in folders:
        if runre.match(d) is not None:
            return(d)
    sys.exit(inspect.stack()[0][3] + ': Cannot find a run identifier from the path:\n%s' %(ori_path))

def slx2fastqname(slx_id, tag= ''):
    """Get the URL associated to the service_id *slx_id* (string) to extract the
    name of the fastq file. The fastq file name is reformatted to contain the associated
    service and run id. The optional tag will be inserted in the file name after
    the run id, separated by dots.
    
    The resulting fastq file will be composed as **<slx>.<runid>.<tag>.<lane>.<ext>**
    
    **Args**
        slx_id (str): Service ID for which to retrieve URL(s).
    
    **Kwargs**
        tag (str): Optional tag to include in the file name.

    **Returns**
        List of filenames, one for each URL.
    
    """
    paths= slx2filepath(slx_id)
    slxs= [slx_id] * len(paths)
    tags= [tag] * len(paths)
    run_ids= [get_runid(x) for x in paths]
    fnames= [os.path.split(x)[1] for x in paths] ## This will return s_1_1_sequence.txt.gz
    ## Make sure you are getting a file named s_x_x_sequence.txt.gz
    #for x in fnames:
    #    if re.match('^s_\d_(\d_)?sequence\.txt\.gz$', x) is None:
    #        sys.exit(inspect.stack()[0][3] + ": File returned by %s doesn't look like a solexa file (s_x_x_sequence.txt.gz): %s" %(slx_id, x))
    fnames= [x.replace('_sequence', '') for x in fnames]
    if tag == '' or tag is None:
        fastq= zip(slxs, run_ids, fnames)
    else:
        fastq= zip(slxs, run_ids, tags, fnames)
    fastq= ['.'.join(x) for x in fastq]
    return(fastq)

def is_multiplexed(slx_id):
    """Queries sblab ``view_multiplexed_services`` to determine whether the service ``slx_id`` is
    multiplexed. 
    
    A service is defined as multiplexed if the associated libraries are barcoded and there is more than
    one index in the service.
    
    Single-plexed services are those where one and only one non barcoded (``non_multiplexed``) libraries is loaded or where
    a single barcoded library is found.
    
    This means that a service with one barcoded library only is not going to be demultiplexed as it is assumed that all the reads
    in the fastq file(s) come from the only library. Consider adding a switch to control this behaviour.
        
    Returns: (bool) True or False.
    
    .. seealso::
        ``view_multiplexed_services`` and other related queries on sblab which are those effectively deciding what a multiplexed
        service is.
    
    .. warning::
        Return False if the service is not multiplxed but also if the service **doesn't** exists! (Probably needs changing)"""
    
    sql= "select * from view_multiplexed_services where service_id = %s and no_libs > 1"
    conn= psycopg2.connect(get_psycopgpass())
    cur= conn.cursor()
    cur.execute(sql, (slx_id,))
    libraries= cur.fetchall()
    cur.close()
    conn.close()
    if len(libraries) == 0:
        return(False)
    else:
        return(True)

def get_library_for_service(slx_id):
    """Queries table lib2seq to fetch the library(s) associated to the SLX-ID (``str``) and return them as **list**.
    Throw exception if the service ID is not found in lib2seq.
    """
    sql= 'select library_id from lib2seq where service_id = %s'
    conn= psycopg2.connect(get_psycopgpass())
    cur= conn.cursor()
    cur.execute(sql, (slx_id,))
    libraries= cur.fetchall()
    conn.close()
    cur.close()
    libraries= [x[0] for x in libraries]
    if len(libraries) == 0:
        sys.exit(inspect.stack()[0][3] + ': Service id "%s" not found in table lib2seq' %(slx_id,))
    return(libraries)
    
def get_fastq_encoding(fastq):
    """Read fastq file to determine the quality encoding. Fastq file can be either
    plain text or gzipped. 
    
    **Returns**:
        String ``Sanger`` or ``Illumina 1.5+`` or ``Undetermined``.
        There is no distinction within Illumina variations and no distictions between Sanger and `Illumina 1.8+ Phred+33`.
        If only ambiguos codes are found (e.g. 'BCDEFGHI') the string ``Undertermined`` is returned 

    .. seealso::

        A sample `perl <https://www.uppnex.uu.se/content/check-fastq-quality-score-format>`_ script using the same approach and 
        `Wikipedia fastq format <http://en.wikipedia.org/wiki/FASTQ_format#Encoding>`_
        
        SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS.....................................................
        ..........................XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX......................
        ...............................IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII......................
        .................................JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ......................
        LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL....................................................
        !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~
        |                         |    |        |                              |                     |
       33                        59   64       73                            104                   126
        0........................26...31.......40                                
                                 -5....0........9.............................40 
                                       0........9.............................40 
                                          3.....9.............................40 
        0........................26...31........41                               
      
       S - Sanger        Phred+33,  raw reads typically (0, 40)
       X - Solexa        Solexa+64, raw reads typically (-5, 40)
       I - Illumina 1.3+ Phred+64,  raw reads typically (0, 40)
       J - Illumina 1.5+ Phred+64,  raw reads typically (3, 40)
           with 0=unused, 1=unused, 2=Read Segment Quality Control Indicator (bold) 
           (Note: See discussion above).
       L - Illumina 1.8+ Phred+33,  raw reads typically (0, 41)

    .. note::
        
        * A file is ``Sanger`` if a quality ascii <59 is found and ``Illumina`` if ascii >74 is found. Files where neither of these ranges are found will throw an exception.
        * NB: There is no check of whether the codes are within the known encodings
          (e.g. no error or warning if you the fastq has qualities like 'lmnopqrstuvwxy')    
    """
    if fastq.endswith('.gz'):
        fin= gzip.open(fastq)
    else:
        fin= open(fastq)
    while True:
        fin.readline()
        fin.readline()
        fin.readline()
        qual= fin.readline().strip()
        if qual == '':
            fin.close()
            return('Undetermined')
            ## sys.exit(inspect.stack()[0][3] + ': Encoding of %s could not be determined after having read the whole file' %(fastq))
        encoding= [ord(x) for x in qual]
        for x in encoding:
            if x < 59:
                fin.close()
                return('Sanger')
            if x > 74:
                fin.close()
                return('Illumina 1.5+')

class IlmnFastqHeader:
    """ Parse Illumina FASTQ header to return the various specs
    These are examples of fastq headers. Note the leading @ is removed:
    HWI-ST230:959:6:1101:1207:2167#NTCACGA/1
    M00893:46:000000000-A1Y65:1:1101:24588:2198 1:N:0:5
    
    EXAMPLE
        fq= sblab.IlmnFastqHeader()
        fq.parseHeader('HWI-ST230:959:6:1101:1207:2167#NTCACGA/1')
        fq.fprint()
            {'fastqHeader': 'HWI-ST230:959:6:1101:1207:2167#NTCACGA/1',
             'knownPlatforms': {'HWI-EAS202': 'Illumina Genome Analyzer IIx',
                                'HWI-EAS350': 'Illumina Genome Analyzer IIx',
                                'HWI-ST230': 'Illumina HiSeq 2000',
                                'M00225': 'Illumina MiSeq'},
             'sequencer_id': 'HWI-ST230',
             'sequencing_platform': 'Illumina HiSeq 2000'}
             
        ## From file:
        fq= sblab.IlmnFastqHeader()
        fqfile= 'mjb035_oxbs_005_hct116.ILMN001.HCT25_S5_L001_R1_001.fastq.gz'
        fq.getHeaderFromFile(fqfile)
        fq.fprint()
            {'fastqHeader': 'M00893:46:000000000-A1Y65:1:1101:24588:2198 1:N:0:5',
            'fastqfile': 'mjb035_oxbs_005_hct116.ILMN001.HCT25_S5_L001_R1_001.fastq.gz',
            'knownPlatforms': {'HWI-EAS202': 'Illumina Genome Analyzer IIx',
                               ...
            'sequencer_id': 'M00893',
            'sequencing_platform': 'Illumina MiSeq'}

    """
    def __init__(self):
        self.fastqfile= None
        self.fastqHeader= None
        self.sequencer_id= None
        self.sequencing_platform= None
        self.knownPlatforms= getSequencingPlatforms() ## Dictionary of sequence IDs and corresponding platforms. Use getSequencingPlatforms to populate it
    def parseHeader(self, fastqHeader):
        """ Parse a FASTQ header to populate the various slots.
        The header is split at colons"""
        header= fastqHeader.split(':')
        self.fastqHeader= fastqHeader
        seqid= header[0].split('_')[0]
        self.sequencer_id= seqid
        if seqid in self.knownPlatforms:
            self.sequencing_platform= self.knownPlatforms[seqid]
        else:
            self.sequencing_platform= None
    def getHeaderFromFile(self, fqfile):
        """Read fastq file fqfile to get the first header line"""
        self.fastqfile= fqfile
        if fqfile.endswith('.gz'):
            fin= gzip.open(fqfile)
        else:
            fin= open(fqfile)
        header= fin.readline().strip()
        if not header.startswith('@'):
            sys.exit('Invalid fastq header found: %s' %(header))
        else:
            header= header[1:]
        self.parseHeader(header)
        fin.close()
    def fprint(self):
        """Print out slots """
        pprint.pprint(self.__dict__)
        
def getSequencingPlatforms():
    """ Query sblab.sequencers to get a dictionary of sequencing_id:platform """
    conn= psycopg2.connect(get_psycopgpass())
    cur= conn.cursor()
    sql= 'SELECT sequencer_id, sequencing_platform FROM sequencers;'
    cur.execute(sql)
    sequencers= cur.fetchall()
    seqdict= {}
    for sid, platid in sequencers:
        seqdict[sid]= platid    
    cur.close()
    conn.close()
    return(seqdict)
    
def downloadService(slx_id, tag= ''):
    """Download the fastq files associated to service ID slx_id (str).

    Files go in the current dir and are renamed using :func:`.slx2fastqname` (see there for tag Kwarg).
    
    It returns a list of downlaoded files.
    
    Possibly to do: Fail if files already exist (currently they are overwritten!)
    """
    paths= slx2filepath(slx_id)
    fastq= slx2fastqname(slx_id, tag= tag)
    for path, fq in zip(paths, fastq):
        cmd= 'scp %s %s' %(path, fq)
        print(cmd)
        p= subprocess.Popen(cmd, shell= True)
        p.wait()
    return(fastq)

def solexa2sanger(fastq, bsub= True, no_db_update= False):
    """.. warning::
    
        Deprecated. Use stand-alone programs instead.
    
    Convert file from solexa to sanger encoding. Input file is renamed
    by replacing .txt.gz with .fq.gz
    Input is expected to be gzipped (ending in .gz). Uploading to sblab is also performed.
        
    If bsub= True, the converison is done on lustre (i.e. you are on lustre).
    If no_db_update= True, output file will not be insrted in fastqfiles table
    IMPORTANT: The fastq file is expected to be DEMULTIPLEXED
        
    EXAMPLE: You have s_1_1_sequence.txt.gz
    - Rename it to include library name eg db001.s_1_1.txt.gz
    - solexa2sanger('db001.s_1_1.txt.gz') ==> Will replace original file with db001.s_1_1.fq.gz and will upload it to sblab.
    """
    ## Check fastq is Illumina:
    encoding= get_fastq_encoding(fastq)
    if encoding == 'Sanger':
        sys.exit(inspect.stack()[0][3] + ': File %s is in Sanger encoding and will not be processed. Exiting...' %(fastq))
    if not fastq.endswith('.txt.gz'):
        sys.exit(inspect.stack()[0][3] + ': Unexpected file name: %s. Fastq file is expected to end in .txt.gz' %(fastq))
    fastq_unzipped= re.sub('\.gz$', '', fastq)
    newname_unizipped= re.sub('\.txt\.gz$', '.fq', fastq)
    # Unzip
    shcmd= 'gunzip %(fastq)s && solexa2phred %(fastq_unzipped)s %(newname_unizipped)s && gzip %(newname_unizipped)s && rm %(fastq_unzipped)s'  %{'fastq': fastq, 'fastq_unzipped': fastq_unzipped, 'newname_unizipped': newname_unizipped}
    if not no_db_update:
         shcmd= shcmd + ' && insert_fastq.py %(newname_unizipped)s.gz' %{'newname_unizipped':newname_unizipped}
    if bsub:
        cmd= 'bsub -J get_fastq_from_slx-%s -oo get_fastq_from_slx.%s.bsub.log "%s"' %(fastq, fastq, shcmd)
    else:
        cmd= shcmd
    print(cmd)
    p= subprocess.Popen(cmd, shell= True)

def get_demux_sheet(service_id, fastqfile):
    """Query sblab to get the demultiplexing info for the given ``service_id`` and ``fastqfile``.

    * ``service_id``:  `service_id` linking multiplexed fastqfile to libraries.
    * ``fastqfile``:   Name of `fastqfile` to be demultiplexed. This name, the service_id and the corresponding library_ids will generate the output file names. 
    
    **Returns**: List of tuples where each tuple has elements:
    
            0. Barcode and output file name to be sent to demux sheet
            1. ``library_id`` corresponding to this demultiplxed file
            2. Fastqfile name that will be generated (w/o .gz even if `demux_fuzzy.py <http://code.google.com/p/bioinformatics-misc/source/browse/trunk/demux_fuzzy.py>`_ will add .gz)


    **Output example**::
    
        [('ATCACGA mjb001_bs_lmw.fastqfile.fq', 'mjb001_bs_lmw', 'mjb001_bs_lmw.fastqfile.fq'),
         ('TTAGGCA mjb002_bs_lmw.fastqfile.fq', 'mjb002_bs_lmw', 'mjb002_bs_lmw.fastqfile.fq')]
    
    To actually produce a demux sheet file that can be passed to ``demux`` or `demux_fuzzy.py <http://code.google.com/p/bioinformatics-misc/source/browse/trunk/demux_fuzzy.py>`_::
    
        d= get_demux_sheet('SLX-5211', 'fastqfile.fq.gz')
        fout= open('fastqfile.fq.demux', 'w')
        for line in d:
            fout.write(line[0] + '\\n')
        fout.close()


    .. note::

        For compatibility with ``demux``, the sample sheet is:
        
        * Space (not tab) separated
        
        * Barcode sequence includes A at 7th position (ignored by `demux_fuzzy.py <http://code.google.com/p/bioinformatics-misc/source/browse/trunk/demux_fuzzy.py>`_)
        
        * Names of demultiplxed files do not end in .gz although `demux_fuzzy.py <http://code.google.com/p/bioinformatics-misc/source/browse/trunk/demux_fuzzy.py>`_ will output gzipped files ending in .gz 
    
    """
    conn= psycopg2.connect(get_psycopgpass())
    cur= conn.cursor()
    basename= fastqfile.rstrip('.gz').rstrip('.txt')
    if not basename.endswith('.fq') and not basename.endswith('fastq'):
        basename= basename + '.fq'
    sql= "select sequence4demux || ' ' || library_id || '.' || %(basename)s, library_id, library_id || '.' || %(basename)s from view_demultiplex_service where service_id = %(service_id)s order by library_id"
    ## print(cur.mogrify(sql, {'basename': basename, 'service_id':service_id}))
    cur.execute(sql, {'basename': basename, 'service_id':service_id})
    demux= cur.fetchall()
    return(demux)
    
def get_fastqc_dir(x):
    """Return the name of the fastqc directory that will be generated by FastQC from the name of the
    fastq file ``x``.

    Essentially: extensions .fastq/.bam./sam/.txt are stripped
    with and without .gz.
    The zipped file is simply `get_fastqc_dir(x) + '.zip'`
    
    **Example**::
    
        >>> get_fastqc_name(myfile.fastq.gz)
        myfile_fastqc
        >>>get_fastqc_name(myfile.fastq/.bam/.sam/.txt)
        myfile_fastqc
        >>>get_fastqc_name(myfile.fq.gz)
        myfile.fq_fastqc
        >>>get_fastqc_name(myfile.fq)
        myfile.fq_fastqc
        
    """
    fastqc= x
    if fastqc.endswith('.gz'):
        fastqc= os.path.splitext(fastqc)[0]
    if fastqc.endswith('.fastq'):
        fastqc= os.path.splitext(fastqc)[0]
    elif fastqc.endswith('.bam'):
        fastqc= os.path.splitext(fastqc)[0]
    elif fastqc.endswith('.sam'):
        fastqc= os.path.splitext(fastqc)[0]
    elif fastqc.endswith('.txt'):
        fastqc= os.path.splitext(fastqc)[0]
    else:
        pass
    fastqc= fastqc + '_fastqc'
    return(fastqc)

def prettyPrintTable(data, header= None):
    """Print on screen a data table nicely formatted for reading on stdout.
    
    data:
        List of lists where inner list is a row. Assumed not to have header. All rows
        must be of the same length!
    header:
        List of headers. If None (defualt) dummy column names will be generated
    
    NOTE:
        See http://stackoverflow.com/questions/6200288/pretty-printing-a-list-in-a-tabular-format
    
    MEMO:
        Output from psycopg2 queries can be printed to screen with:
            cur.execute('select * from mytable') 
            sqldata= cur.fetchall()                        ## List of tuples
            header= print([x[0] for x in cur.description]) ## query headers
            prettyPrintTable(sqldata, header)

    EXAMPLE:
    data = [ [ 'ABCDEFGH', 47, 4, 574862, 58, 7856, 'AGGREGATE_VALUE1'],
             [ 2, 75, 757, 8233, 838, 47775272785, 'AGGREG2'],
             [ 4144, 78, 78965, 778, 78578, 2, 'AGGREGATE_VALUE3']]
    
    prettyPrintTable(data)
    V1       | V2 | V3    | V4     | V5    | V6          | V7              
    ---------+----+-------+--------+-------+-------------+-----------------
    ABCDEFGH | 47 | 4     | 574862 | 58    | 7856        | AGGREGATE_VALUE1
    2        | 75 | 757   | 8233   | 838   | 47775272785 | AGGREG2         
    4144     | 78 | 78965 | 778    | 78578 | 2           | AGGREGATE_VALUE3
        """
    if len(data) == 0:
        return()
    data= [tuple(row) for row in data]
    if header is None:
        header= tuple(['V' + str(i) for i in range(1, (len(data[0])+1))])
        ## header = ('Price1','Price2','reference','XYD','code','resp','AGGREG values')
    else:
        header= tuple(header)
        
    longg = dict(zip(range(0, len(header)), (len(str(x)) for x in header)))
    
    for tu in data:
        longg.update(( i, max(longg[i], len(str(el))) ) for i,el in enumerate(tu))
        #longg[len(header)-1] = max(longg[len(header)-1],len(str(x)))
    
    fofo= ' | '.join('%%-%ss' % longg[i] for i in range(0,len(header)))
    
    var_cols= [str(i) for i in range(0, len(header))]
    headerline= fofo %(header)
    subheader= '-+-'.join( longg[i]*'-' for i in xrange(len(header)))
    datalines= '\n'.join(fofo %(var_cols) for var_cols in data)
    print('\n'.join([headerline, subheader, datalines]))

                 
